Release 10.1A: OpenEdge Development:
Progress Dynamics Basic Development

Data management in the SDO

When the database query is opened, a batch of rows is read into the RowObject temp-table on the server-side SDO, as illustrated in Figure 10–1. This temp-table is then returned to the client-side SDO as an output parameter to the serverSendRows procedure call. This client-side temp-table can then be browsed by SmartDataBrowsers, and individual row values are displayed in one or more SmartDataViewers.

Figure 10–1: Reading the first batch of rows into the SDO

Once the RowObject table has been passed to the client, the server-side copy is emptied in preparation for reading another batch of rows from the database. When the SDO is running in a stateless AppServer session, the norm for deployed Progress Dynamics applications, the server-side SDO is deleted to free the session for another user. If another batch of data is requested, the server-side SDO restarts, the query reopens, and repositions. The data is read from the database into the server-side RowObject table, passed to the client, and appended to the client-side RowObject table. The client-side query on the RowObject table is reopened and repositioned to the new current row (the first row of the new batch). If there is a SmartDataBrowser, the reopening of the RowObject query automatically refreshes the browser.

Remember that all records are read from the database NO-LOCK. This is because there is simply no way to keep record locks on a set of records that is being passed to another Progress session. The SDO code checks to make sure that another user has not modified any updated row, since it was read. Also, there are functions to refresh the data set or the current row if that is desired. Figure 10–2 shows this process.

Figure 10–2: Reading the second batch of rows into the SDO

To collect changes to send back to the server, there is a separate copy of the RowObject temp-table called RowObjUpd. When the user updates an existing row two things happen. First, a row is created in the RowObjUpd table and the original values of the modified row are copied there. This before image of the row allows the SDO to determine later in the process whether another user has changed the row since this user read it. Second, the changes are saved to the RowObject table on the client, and a special flag field, called RowMod, is set to U (for update) to indicate that the row has changed.

The RowMod field in the before-image record in the RowObjUpd table is set to blank, as shown in Figure 10–3.

Figure 10–3: Modifying customer 5 in the client SDO

If the user adds (or copies) a new row, the data is written to the RowObject table, with a RowMod code of A (or C). It might seem puzzling that the changes are first written to the RowObject table on the client rather than to the RowObjUpd table. The reason for this is that any changes need to be visible to the user before they are committed. Thus, they must be made to the table the client objects are browsing (the RowObject table). As discussed below, all the changes are moved into the RowObjUpd table on Commit.

When a user deletes a row, it is also necessary to reflect this in the RowObject table, so that the row appears to be truly gone from the user’s session. For this reason, when a Delete occurs, the row to be deleted is moved to the RowObjUpd table with a RowMod of D, and it is deleted from the RowObject table.

The diagram shown in Figure 10–4 reflects an Add and a Delete done within the same transaction as the first Update.

Figure 10–4: Adding row 9 and deleting row 6 after updating row 5

By default, each individual change (a Save of an Update, or Save of an Add/Copy, or a Delete) goes back to the server-side SDO to be committed to the database. If there is a Commit Panel, however, the AutoCommit SDO property that controls this causes changes to be accumulated in the client-side SDO until Commit is done. Then they all go back to the server together.

When a Commit occurs (either immediately when the user chooses Save if there is no Commit Panel or Commit band in the toolbar, or otherwise when the user chooses), the following happens on the client:

Updated rows are copied from the RowObject table to the RowObjUpd table; thus there is a before image of each modified row (with a RowMod of “”) and an after image (with a RowMod of U).
Added or copied rows are copied from the RowObject table to the RowObjUpd table.
The RowObjUpd table is passed to the server-side SDO as an argument to the serverCommit procedure.

The following then happens on the server-side SDO:

Any preTransactionValidate procedure is run to handle business logic that should occur before the actual database transaction begins.
The database transaction is started.
Any beginTransactionValidate procedure is run to handle business logic that should occur inside the transaction but before the changes themselves are written to the database.
The updated row is written back to the database. Each updated row has a RowIdent field in the RowObjUpd table that holds the ROWID records of the database records the row was derived from. The database records are read EXCLUSIVE-LOCK and compared with the before record in the table. If another user has changed the database record, the current change is rejected, unless the CheckCurrentChanged SDO instance property has been set to NO. If nothing has failed so far, those fields that were modified are assigned back to the corresponding database records.
The added row is created in the database and its values assigned.
The row marked to be deleted is read from the database and deleted.
Any endTransactionValidate procedure in the SDO is executed to handle business logic that should occur inside the database transaction but after all the modifications are made to the database, as follows:



The updated and added rows are re-read from the database, to capture any changes made by database triggers or other code (such as the assigning of a key field value by a CREATE trigger, or the calculation of a field by a WRITE trigger, or by one of the TransactionValidate procedures).
Any postTransactionValidate procedure is executed on the server.
The final versions of the possibly modified updated and added rows are passed back to the client, and the final versions are copied back into the RowObject table to be displayed in the client.
The server-side RowObjUpd table is emptied.
Note: When AutoCommit is enabled and changes in one SDO trigger a Commit action, all unsaved changes in all the SBO’s SDO’s are saved.

The following happens on the client:

The RowMod flags in the client-side RowObject table are cleared.
The client-side RowObjUpd table is emptied as shown: